/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vzone.community;

import com.mysql.jdbc.Connection;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import vzone.sql.SqlHelper;
import vzone.structs.BasePeople;
import vzone.structs.OperatorCode;

/**
 *
 * @author Administrator
 */
public class CommunityControl implements ICommunityControl {

    private static final String IP = "localhost";
    private static final int PORT = 3306;
    private static final String DB = "community";
    private static final String USER = "root";
    private static final String PWD = "ogretzar";
    //登录时根据昵称查询数据库
    private static final String SELECT_PEOPLE = "SELECT * FROM  people WHERE `nickName`=@name";
    private static final String SELECT_PEOPLE2 = "SELECT * FROM  people WHERE `idPeople`=@id";
    //根据人的idJob字段来查询人对应的工作信息
    private static final String SELECT_JOB = "SELECT * FROM job WHERE `idJob`=@jobId";
    private static final String GET_PEOPLE_ALL = "SELECT * FROM  people";
    private static final String INSERT_PEOPLE = "INSERT INTO people (`idPeople`, `idAccount`, `idJob`, `name`, `nickName`, `gender`, `birthday`, `pwdHash`)" +
            " VALUES(@id1, @id2, @id3, @name, @nickName, @gender, @birthday, @pwdHash);";
    private static final String UPDATE_PEOPLE = "UPDATE people SET idAccount=@id1，name=@nameS，nickName=@nick,gender=@g，birthday=@b WHERE idPeople=@id";
    private static final String INSERT_JOB = "INSERT INTO job (`idJob`, `idCompany`, `jobName`, `startTimePerDay`, `endTimePerDay`, `jobDescription`, `pay`) " +
            "VALUES(@id1, @id2, @name, @startTime, @endTimePerDay, @description, @pay);";
    private static final String DELETE_PEOPLE = "DELETE FROM people WHERE idPeople`=@id";
    private static final String DELETE_JOB = "DELETE FROM job WHERE idJob`=@id";

    @Override
    public OperatorCode Login(String NickNameS, String pwdHash, BasePeople people) {
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);
        ArrayList resultAl = null;
        ArrayList resultJob = null;
        try {
            resultAl = SqlHelper.ExecuteReader(conn, SELECT_PEOPLE, new Object[]{NickNameS});
        } catch (Exception ex) {
            Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
        }
        Iterator<Object> resultI = resultAl.iterator();

        if (resultI.hasNext()) {
            //用户存在
            while (resultI.hasNext()) {
                Object[] resultOs = (Object[]) resultI.next();
                if (pwdHash == resultOs[7]) {
                    //此处相关类型转换需要确定such as "varchar" to "String"
                    people.PeopleIdUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[0]);
                    people.accountIdUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[1]);

                    people.NameS = (String) resultOs[3];//！
                    people.NickName = NickNameS;
                    people.GenderB = (Boolean) resultOs[5];

                    people.JobJ.JobIDUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[2]);

                    try {
                        resultJob = SqlHelper.ExecuteReader(conn, SELECT_JOB, new Object[]{people.JobJ.JobIDUuid});
                    } catch (Exception ex) {
                        Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    Iterator<Object> resultI2 = resultJob.iterator();
                    Object[] resultOs2 = (Object[]) resultI2.next();
                    people.JobJ.CompanyIDUuid = UUID.nameUUIDFromBytes((byte[]) resultOs2[1]);
                    people.JobJ.JobNameS = (String) resultOs2[3];
                    //其他job属性暂时不添加




                }
            }
        } else {
            //提示用户不存在
        }
        return OperatorCode.INVALIEDPWD;//密码错误
    }

    @Override
    public OperatorCode CheckInfo(UUID peopleIDUuid, ArrayList<BasePeople> peoples) {
        ArrayList resultAl = null;

        BasePeople people = null;
        ArrayList resultJob = null;

        if (isAdmin(peopleIDUuid)) {
            Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);
            try {
                resultAl = SqlHelper.ExecuteReader(conn, GET_PEOPLE_ALL, new Object[]{});
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            Iterator<Object> resultI = resultAl.iterator();

            while (resultI.hasNext()) {
                Object[] resultOs = (Object[]) resultI.next();
                people.PeopleIdUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[0]);
                people.accountIdUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[1]);

                people.NameS = (String) resultOs[3];//！
                people.NickName = (String) resultOs[4];
                people.GenderB = (Boolean) resultOs[5];

                people.JobJ.JobIDUuid = UUID.nameUUIDFromBytes((byte[]) resultOs[2]);

                try {
                    resultJob = SqlHelper.ExecuteReader(conn, SELECT_JOB, new Object[]{people.JobJ.JobIDUuid});
                } catch (Exception ex) {
                    Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
                }
                Iterator<Object> resultI2 = resultJob.iterator();
                Object[] resultOs2 = (Object[]) resultI2.next();
                people.JobJ.CompanyIDUuid = UUID.nameUUIDFromBytes((byte[]) resultOs2[1]);
                people.JobJ.JobNameS = (String) resultOs2[3];
                //其他job属性暂时不添加

                peoples.add(people);
            }
            return OperatorCode.SUCCESS;
        } else {
            return OperatorCode.NOPOWER;
        }
    }

    @Override
    public OperatorCode AddInfo(BasePeople people, String pwdHashS) {
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);

        try {
            SqlHelper.ExecuteNonQuery(conn, INSERT_PEOPLE, new Object[]{
                        UUID.randomUUID(), people.accountIdUuid, people.JobJ.JobIDUuid, people.NameS, people.NickName, people.GenderB, people.BirthdayD, pwdHashS
                    });
            SqlHelper.ExecuteNonQuery(conn, INSERT_JOB, new Object[]{
                        people.JobJ.JobIDUuid
                    });

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return OperatorCode.SUCCESS;
    }

    @Override
    public OperatorCode ChangeInfo(BasePeople people) {
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);
        int i = 0;
        try {
            i = SqlHelper.ExecuteNonQuery(conn, UPDATE_PEOPLE, new Object[]{
                        people.accountIdUuid, people.NameS, people.NickName, people.GenderB, people.BirthdayD
                    });
        } catch (Exception ex) {
            Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (i == 0) {
            return OperatorCode.OTHER_ERROR;
        } else {
            return OperatorCode.SUCCESS;
        }
    }

    @Override
    public OperatorCode RemoveInfo(UUID peopleIDUuid) {
        Connection conn = (Connection) SqlHelper.getConnect(IP, PORT, DB, USER, PWD);
        ArrayList result = null;
        try {
            result = SqlHelper.ExecuteReader(conn, SELECT_PEOPLE2, new Object[]{peopleIDUuid});
        } catch (Exception ex) {
            Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
        }
        Iterator<Object> resultI = result.iterator();
        if (resultI.hasNext()) {
            //用户存在

            Object[] resultOs = (Object[]) resultI.next();
            UUID idJob=UUID.nameUUIDFromBytes((byte[]) resultOs[2]);

            try {
                SqlHelper.ExecuteNonQuery(conn,DELETE_JOB, new Object[]{idJob});
            } catch (Exception ex) {
                Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
            }


        } else {
            //提示用户不存在
            return OperatorCode.NOPEOPLE;
        }
        try {
                SqlHelper.ExecuteNonQuery(conn,DELETE_PEOPLE, new Object[]{peopleIDUuid});
                return OperatorCode.SUCCESS;
            } catch (Exception ex) {
                Logger.getLogger(Community.class.getName()).log(Level.SEVERE, null, ex);
            }
        return  OperatorCode.OTHER_ERROR;
    }

//判断是否为居民社区管理员
    private boolean isAdmin(UUID peopleIDUuid) {
        throw new UnsupportedOperationException("Not yet implemented");
    }
}
